﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace ProjManagSolution.Web
{
    public class Models
    {
        modelDataContext _db;

        public Models()
        {
            this._db = new modelDataContext();
        }

        public void insertUser(user newUser)
        {
            // add the new object to database.
            _db.users.InsertOnSubmit(newUser);
            _db.SubmitChanges();
        }

        public List<user> getUsers(string search = null)
        {
            var q = (
                    from r in _db.users
                    select r
                );
            if (!string.IsNullOrEmpty(search))
            {
                q = (
                    from r in _db.users
                    where (r.user_firstname + " " + r.user_lastname).Contains(search)
                        || (r.user_lastname + " " + r.user_firstname).Contains(search)
                        || r.user_email.Contains(search)
                    select r
                );
            }

            List<user> rows = new List<user>();
            foreach (var r in q)
            {
                rows.Add(r);
            }

            return rows;
        }

        public user getUserDetail(int userId)
        {
            var q = (from r in _db.users where r.user_id == userId select r);

            user row = new user();
            foreach (var r in q)
            {
                row = r;
                break;
            }

            return row;
        }

        public user getUserDetailByAuth(string email, string password)
        {
            var q = (
                from r in _db.users
                where (r.user_email == email && r.user_password == password)
                select r
            );

            user row = new user();
            foreach (var r in q)
            {
                row = r;
                break;
            }

            return row;
        }

        public user getUserDetailByEmail(string userEmail)
        {
            var q = (from r in _db.users where r.user_email == userEmail select r);

            user row = new user();
            foreach (var r in q)
            {
                row = r;
                break;
            }

            return row;
        }

        public string getUserRole(int userId, int projectId)
        {
            var q = (from r in _db.work_ons where r.user_id == userId && r.project_id == projectId select r).Single();

            return q.role;
        }

        public void updateUserDetail(int userId, user updatedUser)
        {
            // reference to db
            user selectedUser = _db.users.Single(u => u.user_id == userId);

            // data to update
            selectedUser.user_address = updatedUser.user_address;
            selectedUser.user_birthday = updatedUser.user_birthday;
            selectedUser.user_email = updatedUser.user_email;
            selectedUser.user_firstname = updatedUser.user_firstname;
            selectedUser.user_lastname = updatedUser.user_lastname;
            selectedUser.user_password = updatedUser.user_password;
            selectedUser.user_phone = updatedUser.user_phone;
            selectedUser.user_sex = updatedUser.user_sex;

            // submit changes
            _db.SubmitChanges();
        }

        public void insertProject(project newProject)
        {
            // add the new object to database.
            _db.projects.InsertOnSubmit(newProject);
            _db.SubmitChanges();
        }

        public void insertProjectNew(int managerid, string projectname, string projectdes, DateTime start, DateTime end)
        {
            project newProject = new project
            {
                manager_id = managerid,
                project_name = projectname,
                project_description = projectdes,
                project_start_date = start,
                project_end_date = end
            };

            // add the new object to database.
            _db.projects.InsertOnSubmit(newProject);
            _db.SubmitChanges();
        }

        public List<project> getProjects(string search = null)
        {
            var q = (
                from r in _db.projects
                select r
            );
            if (!string.IsNullOrEmpty(search))
            {
                q = (
                    from r in _db.projects
                    where r.project_name.Contains(search) || r.project_description.Contains(search)
                    select r
                );
            }

            List<project> rows = new List<project>();
            foreach (var r in q)
            {
                rows.Add(r);
            }

            return rows;
        }

        public List<project> getProjectsByManager(int managerId, string search = null)
        {
            var q = (
                from r in _db.projects
                where r.manager_id == managerId
                select r
            );
            if (!string.IsNullOrEmpty(search))
            {
                q = (
                    from r in _db.projects
                    where r.manager_id == managerId
                        && (r.project_name.Contains(search) || r.project_description.Contains(search))
                    select r
                );
            }

            List<project> rows = new List<project>();
            foreach (var r in q)
            {
                rows.Add(r);
            }

            return rows;
        }

        public List<project> getInvolvedProjects(int userId, string search = null)
        {
            var q = (
                from p in _db.projects
                join w in _db.work_ons on p.project_id equals w.project_id
                where w.user_id == userId
                select p
            );
            if (!string.IsNullOrEmpty(search))
            {
                q = (
                    from p in _db.projects
                    where p.project_name.Contains(search) || p.project_description.Contains(search)
                    join w in _db.work_ons on p.project_id equals w.project_id
                    where w.user_id == userId
                    select p
                );
            }

            List<project> rows = new List<project>();
            foreach (var r in q)
            {
                rows.Add(r);
            }

            return rows;
        }

        public project getProjectDetail(int projectId)
        {
            var q = (from r in _db.projects where r.project_id == projectId select r);

            project row = new project();
            foreach (var r in q)
            {
                row = r;
                break;
            }

            return row;
        }

        public void updateProjectDetail(int projectId, project updatedProject)
        {
            // reference to db
            project selectedProject = _db.projects.Single(p => p.project_id == projectId);

            // data to update
            selectedProject.project_description = updatedProject.project_description;
            selectedProject.project_end_date = updatedProject.project_end_date;
            selectedProject.project_end_date = updatedProject.project_end_date;
            selectedProject.project_name = updatedProject.project_name;
            selectedProject.project_start_date = updatedProject.project_start_date;

            // submit changes
            _db.SubmitChanges();
        }

        public void deleteProject(int projectId)
        {
            // reference to db
            project selectedRow = _db.projects.Single(r => r.project_id == projectId);

            _db.projects.DeleteOnSubmit(selectedRow);
            _db.SubmitChanges();
        }

        public void insertFolder(int projectID)
        {
            // Check if folder exists
            var q = (from r in _db.folders where r.folder_name == projectID.ToString() select r);
            List<folder> fl = q.ToList();
            if (fl.Count > 0)
                return;

            // new project
            folder newFolder = new folder
            {
                project_id = projectID,
                folder_name = projectID.ToString()
            };

            // add the new object to database.
            _db.folders.InsertOnSubmit(newFolder);
            _db.SubmitChanges();
        }

        public List<folder> getFolders(int projectId)
        {
            var q = (from r in _db.folders where r.project_id == projectId select r);

            List<folder> rows = new List<folder>();
            foreach (var r in q)
            {
                rows.Add(r);
            }

            return rows;
        }

        public void updateFolderDetail(int folderId, folder updatedFolder)
        {
            // reference to db
            folder selectedFolder = _db.folders.Single(f => f.folder_id == folderId);

            // data to update
            selectedFolder.folder_name = updatedFolder.folder_name;

            // submit changes
            _db.SubmitChanges();
        }

        public void deleteFolder(int folderId)
        {
            // reference to db
            folder selectedRow = _db.folders.Single(r => r.folder_id == folderId);

            _db.folders.DeleteOnSubmit(selectedRow);
            _db.SubmitChanges();
        }

        public void insertDocumentation(int uploaderID , int projectID , string documentationName , string url , DateTime date , string status)
        {
            int folderID ;
            // Convert projectID to folderID
            var q = (from r in _db.folders where r.folder_name == projectID.ToString() select r);
            List<folder> fl = q.ToList();
            folderID = fl[0].folder_id;

            //new Documentation to upload
            documentation newDocumentation = new documentation
            {
                uploader_id = uploaderID,
                folder_id = folderID,
                documentation_name = documentationName,
                documentation_url = url,
                documentation_date = date,
                documentation_status = status
            };

            // add the new object to database.
            _db.documentations.InsertOnSubmit(newDocumentation);
            _db.SubmitChanges();
        }

        public List<documentation> getDocumentations(int folderId)
        {
            var q = (from r in _db.documentations where r.folder_id == folderId && r.documentation_status == "active" select r);

            List<documentation> rows = new List<documentation>();
            foreach (var r in q)
            {
                rows.Add(r);
            }

            return rows;
        }

        public void deleteDocumentation(int documentationId)
        {
            // reference to db
            documentation selectedDocumentation = _db.documentations.Single(t => t.documentation_id == documentationId);

            // data to update
            selectedDocumentation.documentation_status = "Inactive";

            // submit changes
            _db.SubmitChanges();

            //// reference to db
            //documentation selectedRow = _db.documentations.Single(r => r.documentation_id == documentationId);

            //_db.documentations.DeleteOnSubmit(selectedRow);
            //_db.SubmitChanges();
        }

        public void inviteUser(int userId, int projectId, string role)
        {
            // prepare new record for table work_on
            work_on newWorkOn = new work_on();
            newWorkOn.user_id = userId;
            newWorkOn.project_id = projectId;
            newWorkOn.role = role;

            // add the new object to database.
            _db.work_ons.InsertOnSubmit(newWorkOn);
            _db.SubmitChanges();
        }

        public void reassignMemberRole(int userId, int projectId, string role)
        {
            // reference to db
            work_on selectedWorkOn = _db.work_ons.Single(w => w.user_id == userId && w.project_id == projectId);

            // data to update
            selectedWorkOn.role = role;

            // submit changes
            _db.SubmitChanges();
        }

        public void rejectMember(int userId, int projectId)
        {

        }

        public void insertMileStone(int projectid, string mname, string mdesc, DateTime start, DateTime end)
        {
            milestone newMS = new milestone
            {
                project_id = projectid,
                milestone_name = mname,
                milestone_description = mdesc,
                milestone_start_date = start,
                milestone_end_date = end
            };

            // add the new object to database.
            _db.milestones.InsertOnSubmit(newMS);
            _db.SubmitChanges();
        }

        public List<milestone> getMileStones(int projectId)
        {
            var q = (from r in _db.milestones where r.project_id == projectId select r);

            List<milestone> rows = new List<milestone>();
            foreach (var r in q)
            {
                rows.Add(r);
            }

            return rows;
        }

        public void updateMilestoneDetail(int milestoneId, milestone updatedMilestone)
        {
            // reference to db
            milestone selectedMilestone = _db.milestones.Single(m => m.milestone_id == milestoneId);

            // data to update
            selectedMilestone.milestone_name = updatedMilestone.milestone_name;
            selectedMilestone.milestone_description = updatedMilestone.milestone_description;
            selectedMilestone.milestone_start_date = updatedMilestone.milestone_start_date;
            selectedMilestone.milestone_end_date = updatedMilestone.milestone_end_date;

            // submit changes
            _db.SubmitChanges();
        }

        public void deleteMilestone(int milestoneId)
        {
            // reference to db
            milestone selectedRow = _db.milestones.Single(r => r.milestone_id == milestoneId);

            _db.milestones.DeleteOnSubmit(selectedRow);
            _db.SubmitChanges();
        }

        public void insertPhase(int mid, string pname, string pdesc, DateTime start, DateTime end)
        {
            phase newPhase = new phase
            {
                milestone_id = mid,
                phase_name = pname,
                phase_description = pdesc,
                phase_start_date = start,
                phase_end_date = end
            };

            // add the new object to database.
            _db.phases.InsertOnSubmit(newPhase);
            _db.SubmitChanges();
        }

        public List<phase> getPhases(int milestoneId)
        {
            var q = (from r in _db.phases where r.milestone_id == milestoneId select r);

            List<phase> rows = new List<phase>();
            foreach (var r in q)
            {
                rows.Add(r);
            }

            return rows;
        }

        public void updatePhaseDetail(int phaseId, phase updatedPhase)
        {
            // reference to db
            phase selectedPhase = _db.phases.Single(ph => ph.phase_id == phaseId);

            // data to update
            selectedPhase.phase_name = updatedPhase.phase_name;
            selectedPhase.phase_description = updatedPhase.phase_description;
            selectedPhase.milestone_id = updatedPhase.milestone_id;
            selectedPhase.phase_start_date = updatedPhase.phase_start_date;
            selectedPhase.phase_end_date = updatedPhase.phase_end_date;

            // submit changes
            _db.SubmitChanges();
        }

        public void deletePhase(int phaseId)
        {
            // reference to db
            phase selectedRow = _db.phases.Single(r => r.phase_id == phaseId);

            _db.phases.DeleteOnSubmit(selectedRow);
            _db.SubmitChanges();
        }

        public void insertTask(int pid, string taskname, string taskdesc, DateTime start, DateTime end, string report, int complete)
        {
            task newTask = new task
            {
                phase_id = pid,
                task_name = taskname,
                task_description = taskdesc,
                task_start_date = start,
                task_end_date = end,
                task_report_progress = report,
                task_completed = complete
            };

            // add the new object to database.
            _db.tasks.InsertOnSubmit(newTask);
            _db.SubmitChanges();
        }

        public List<task> getTasks(int phaseId)
        {
            var q = (from r in _db.tasks where r.phase_id == phaseId select r);

            List<task> rows = new List<task>();
            foreach (var r in q)
            {
                rows.Add(r);
            }

            return rows;
        }

        public task getTaskDetail(int taskId)
        {
            var q = (from r in _db.tasks where r.task_id == taskId select r);

            task row = new task();
            foreach (var r in q)
            {
                //this.formatObjTask(r);
                row = r;
                break;
            }

            return row;
        }

        public void updateTaskDetail(int taskId, task updatedTask)
        {
            // reference to db
            task selectedTask = _db.tasks.Single(t => t.task_id == taskId);

            // data to update
            selectedTask.task_name = updatedTask.task_name;
            selectedTask.task_description = updatedTask.task_description;
            selectedTask.task_start_date = updatedTask.task_start_date;
            selectedTask.task_end_date = updatedTask.task_end_date;
            selectedTask.task_report_progress = updatedTask.task_report_progress;
            selectedTask.task_completed = updatedTask.task_completed;

            // submit changes
            _db.SubmitChanges();
        }

        public void deleteTask(int taskId)
        {
            // reference to db
            task selectedRow = _db.tasks.Single(r => r.task_id == taskId);

            _db.tasks.DeleteOnSubmit(selectedRow);
            _db.SubmitChanges();
        }

        public List<user> getProjectMembers(int projectId, string search = null)
        {
            var q = (
                from u in _db.users
                join w in _db.work_ons on u.user_id equals w.user_id
                where w.project_id == projectId
                select u
            );
            if (!string.IsNullOrEmpty(search))
            {
                q = (
                    from u in _db.users
                    where (u.user_firstname + " " + u.user_lastname).Contains(search)
                        || (u.user_lastname + " " + u.user_firstname).Contains(search)
                    join w in _db.work_ons on u.user_id equals w.user_id
                    where w.project_id == projectId
                    select u
                );
            }
            List<user> rows = new List<user>();
            foreach (var r in q)
            {
                rows.Add(r);
            }

            return rows;
        }

        private void formatObjUser(user raw)
        {
            raw.projects = null;
            raw.assigns = null;
            raw.documentations = null;
            raw.work_ons = null;
        }

        private void formatObjProject(project raw)
        {
            raw.user = null;
            raw.work_ons = null;
            raw.folders = null;
            raw.milestones = null;
        }

        private void formatObjDocument(documentation raw)
        {
            raw.user = null;
            raw.folder = null;
        }

        private void formatObjFolder(folder raw)
        {
            raw.documentations = null;
            raw.project = null;
        }

        private void formatObjMilestone(milestone raw)
        {
            raw.project = null;
            raw.phases = null;
        }

        private void formatObjPhase(phase raw)
        {
            raw.milestone = null;
            raw.tasks = null;
        }

        private void formatObjTask(task raw)
        {
            raw.assigns = null;
            raw.phase = null;
        }
    }
}